package com.apop.common.util;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @ClassName SQLLogUtil
 * @author Jiayi Zheng
 * @date 2021/11/10 15:35
 * @Description: SQL 日志替换工具 可以将占位符 ？ 替换为参数
 *  用途：用于异常SQL排除错误
 * @version V1.0
 */
public class SQLLogUtil {

//    static final String sql =
//        "insert into T_PSM_UnempRgt (ID, SERVICEREQID, FDATASOUR, ADC201, AAB301, FPROVINCE, FCITY, FCOUNTY, FREGISTERDATE, FNAME, FSEX, FBIRTH, FNATION, FPOLSTAT, FEDUBACK, FHEALSTATE, FDISABLE, FUNEMPTIME, AAC002, FCERCAT, AAC044, FIDNUM, FIDELSE, AAB299, FRESIDPROVINCE, FRESIDCITY, FRESIDCOUNTY, FRESIDTOWN, FRESIDVILLAGE, FRESIDDETA, AAF016, FPERPROVINCE, FPERCITY, FPERCOUNTY, FPERTOWN, FPERVILLAGE, FPERDETA, FPERADD, ACA111, AAC015, ACA200, ACA202, ACA201, ACA203, AAC200, AAC014, ACA204, ACA206, ACA205, ACA207, FPHONUM, FPHO, FEMAIL, FOTHER, FREGISTYPE, FUNEMPREA, ADC220, ACA112, FREMARK, FISAPPLYUNEMP,FFeedBack) with dj1 as( SELECT f.fprovince,f.fcity,f.id fcounty FROM T_PSM_AreaInfoFive f WHERE f.fcode=? ||'000000' and f.FType=3 and f.fstatus=1),dj as(SELECT fprovince,fcity,fcounty FROM dj1 union all SELECT f.fprovince,f.fcity,null fcounty FROM T_PSM_AreaInfoFive f  WHERE f.fcode=? ||'000000' and f.FType=2 and f.fstatus=1  and not exists(SELECT 1 FROM dj1)) ,hj as( SELECT b.fprovince,b.fcity,b.fcounty, (case when b.ftype>=4 then b.ftown else null end) ftown,(case when b.ftype>=5 then b.id else null end) FVillage FROM T_PSM_AreaInfoFive b WHERE b.fcode=? and b.fstatus=1 and b.ftype=(SELECT max(a.ftype) FROM T_PSM_AreaInfoFive a WHERE a.fcode=? and a.fstatus=1) ),cz as( SELECT b1.fprovince,b1.fcity,b1.fcounty, (case when b1.ftype>=4 then b1.ftown else null end) ftown,(case when b1.ftype>=5 then b1.id else null end) FVillage FROM T_PSM_AreaInfoFive b1  WHERE b1.fcode=? and b1.fstatus=1 and b1.ftype=(SELECT max(a1.ftype) FROM T_PSM_AreaInfoFive a1 WHERE a1.fcode=? and a1.fstatus=1) ),dz as( SELECT to_char(replace(wm_concat(b2.fname),',','')) FAdd FROM( SELECT a2.fname,max(a2.ftype) FROM T_PSM_AreaInfoFive a2 inner join cz on cz.fprovince=a2.id or cz.fcity=a2.id or cz.fcounty=a2.id or cz.ftown=a2.id or cz.FVillage=a2.id WHERE a2.fstatus=1 group by a2.fname order by max(a2.ftype))b2 ) select FUNC_NEXTID('T_PSM_UnempRgt') ID, ? ,  3 FDATASOUR, ?, ?, (SELECT dj.fprovince FROM dj where rownum=1) FPROVINCE, (SELECT dj.fcity FROM dj where rownum=1) FCITY,  (SELECT dj.fcounty FROM dj where rownum=1) FCOUNTY, to_date(?, 'yyyymmdd') , ?, ?, to_date(?, 'yyyymmdd'), ?, ?, ?, ?, ?,  to_date(?, 'yyyymmdd'), ?, ?, ?, decode(? ,'01',?,null) FIDNUM, decode(? ,'01',null,?) FIDELSE, ?, (SELECT hj.fprovince FROM hj where rownum=1) FRESIDPROVINCE, (SELECT hj.fcity FROM hj where rownum=1) FRESIDCITY, (SELECT hj.fcounty FROM hj where rownum=1) FRESIDCOUNTY, (SELECT hj.ftown FROM hj where rownum=1) FRESIDTOWN, (SELECT hj.FVillage FROM hj where rownum=1) FRESIDVILLAGE, ?,  ?, (SELECT cz.fprovince FROM cz where rownum=1) FPERPROVINCE,  (SELECT cz.fcity FROM cz where rownum=1) FPERCITY, (SELECT cz.fcounty FROM cz where rownum=1) FPERCOUNTY,  (SELECT cz.ftown FROM cz where rownum=1) FPERTOWN, (SELECT cz.FVillage FROM cz where rownum=1) FPERVILLAGE,  ?, (SELECT dz.FAdd FROM dz where rownum=1)||?,  ?,?,?,?,?,?,?,?,?,?,?,?,  ?, ?, ?,  ?, ?, ?,  ?, ?,  ?, ?,0 FFeedBack  FROM dual  where not exists(SELECT 1 FROM T_PSM_UnempRgt r1 where r1.ADC201=? )   ";
//    static String param =
//            " [1:\"350100\", 2:\"350100\", 3:\"350100000000\", 4:\"350100000000\", 5:\"350100000000\", 6:\"350100000000\", 7:\"1120211015000000005\", 8:\"ddcda857-9560-4dd1-831b-2eb70e8517ba\", 9:\"350100\", 10:\"20200227\", 11:\"姓名9991510\", 12:1, 13:\"19930604\", 14:\"01\", 15:\"01\", 16:14, 17:1, 18:\"02\", 19:20200224, 20:\"360601199201012094\", 21:\"01\", 22:\"360601199201012094\", 23:\"01\", 24:\"360601199201012094\", 25:\"01\", 26:\"360601199201012094\", 27:\"350100000000\", 28:\"户籍地详细地址乡镇、街道、社区（村）、楼门号等\", 29:\"350100000000\", 30:\"户籍地详细地址乡镇、街道、社区（村）、楼门号等\", 31:\"户籍地详细地址乡镇、街道、社区（村）、楼门号等\", 32:\"4029900\", 33:\"1\", 34:\"4020401\", 35:\"2\", 36:\"3010208\", 37:\"3\", 38:\"011\", 39:\"410\", 40:\"012\", 41:\"411\", 42:\"013\", 43:\"412\", 44:\"13305903185\", 45:\"0592-88888888\", 46:\"123@qq.com\", 47:\"其他联系方式\", 48:1, 49:22, 50:\"\", 51:\"求职意向一 如：销售经理|求职意向二 如：销售经理|求职意向三 如：销售经理\", 52:\"其他需要说明的事项\", 53:1, 54:\"ddcda857-9560-4dd1-831b-2eb70e8517ba\"]";

   static final String sql = "insert into T_PSM_WORKFLOW (ID, FSYSTEMID, FBUSINESSID, FNAME, FNo, FAPPLYUSER, FIDCARDNUM, FAPPLYTIME, FAPPLYDEPT, FACCEPTTIME, FSTATUS)values (FUNC_NEXTID('T_PSM_WORKFLOW'), ?, ?, ?, ?, ?, ?, ?, ?, ? ,'1') ";

    static String param =
            "  [1:\"ZHJY\", 2:\"8\", 3:\"关于李思明《就业创业证》申领的申请\", 4:\"2021030810011\", 5:\"李思明\", 6:\"350102199901010101\", 7:\"2021-11-23\", 8:\"福州市鼓楼区人力资源社会保障局\", 9:\"2021-11-23\"]  ";

    public static void main(String[] args){

        param = param.replaceFirst("\\[","");
        param = param.replaceFirst("\\]","");
        String[] part = param.split(",");

//        System.out.println("sql >> \n"+sql);
//        System.out.println("param >> \n"+param);
        String sNew = sql;
        int i = 1;
        for(String s : part){
            //System.out.println(s);

            Pattern p=Pattern.compile("\"(.*?)\"");

            Matcher m=p.matcher(s);
            if(m.find()){
                String group = m.group();
                // 只提取双引号部分
                String keypart =group.substring(1,group.length()-1);
                sNew = sNew.replaceFirst("\\?","'"+keypart+"'");
                i++;
            }
            else{
                String keypart = s.substring(s.indexOf(":")+1);
                sNew = sNew.replaceFirst("\\?","'"+keypart+"'");
                i++;
            }
        }
        System.out.println("======================================== ");
        System.out.println(sNew);
        System.out.println("======================================== \n");
    }

}
